<?php

function getPlayerScore($playerId, $roundId, $isArchive) {
 	$sql = "select score from PlayerScore" . getTableExtension($isArchive) . " ps where playerId = $playerId and roundId = $roundId";
   	$result = mysql_query($sql);
   	
   	$row = mysql_fetch_array($result);
   	$score = 0;
   	if ($row != null) {
   		$score = $row["score"];
   	}
   	
      	
	return $score;
}

function getPlayerScores($teamId, $roundId, $isArchive) {
 	$sql = "select * from playerscore" . getTableExtension($isArchive) . " ps inner join player" . getTableExtension($isArchive) . " p on ps.playerId = p.id " .
 	" where roundId = $roundId and playerId in (select tp.playerId from playerforround" . getTableExtension($isArchive) . " pfr " .
 	" inner join teamplayer" . getTableExtension($isArchive) . " tp on pfr.playerId = tp.id " .
 	" where tp.teamId = $teamId and roundId = $roundId and tp.traded = false and pfr.replacementPlayerId is null and emergency = false)";
  	$result = mysql_query($sql);
  	
	$array = array();
	while($row = mysql_fetch_array($result)) {
		$playerScore = new PlayerScore();
		$player = new Player();
		$player->playerFirstName = $row['firstName'];
		$player->playerSurname = $row['surname'];
		$player->team = $row['team'];
		$player->playerType = $row['playerType'];
		$playerScore->player = $player;
		$playerScore->score = $row['score'];
		
		$array[] = $playerScore;
	}
	
	$sql = "select * from playerscore" . getTableExtension($isArchive) . " ps inner join player" . getTableExtension($isArchive) . " p on ps.playerId = p.id " .
 	" where roundId = $roundId and playerId in (select tp.playerId from playerforround" . getTableExtension($isArchive) . " pfr " .
 	" inner join teamplayer" . getTableExtension($isArchive) . " tp on pfr.replacementPlayerId = tp.id " .
 	" where tp.teamId = $teamId and roundId = $roundId and tp.traded = false and pfr.replacementPlayerId is not null and emergency = false)";
  	$result = mysql_query($sql);
	
	while($row = mysql_fetch_array($result)) {
		$playerScore = new PlayerScore();
		$player = new Player();
		$player->playerFirstName = $row['firstName'];
		$player->playerSurname = $row['surname'];
		$player->team = $row['team'];
		$player->playerType = $row['playerType'];
		$playerScore->player = $player;
		$playerScore->score = $row['score'];
		
		$array[] = $playerScore;
	}
	
	usort($array, "comparePlayerScore");
	
       	
	return $array;
}

function getTeamScoresByAverage($league, $round) {
	$sql = "select p.firstName, p.surname, p.team, p.playerType, ps.score, t.id as teamId, t.teamName from playerscore ps inner join player p on ps.playerId = p.id inner join teamplayer tp on tp.playerId = p.id ";
	$sql .= "and traded = false inner join team t on tp.teamId = t.id where t.leagueId = $league and ps.round in ($round);";
	
	$result = mysql_query($sql);
	
	$playerScores = array();
	$teamPlayerScores = array();
	$lastTeam = "";
	$teamPlayerScore = null;
	while($row = mysql_fetch_array($result)) {
		$teamName = $row['teamName'];
		if ($lastTeam != $teamName) {
			if ($teamPlayerScore != null) {
				usort($playerScores, "comparePlayerScore");
				
				$teamPlayerScore->playerScores = $playerScores;
				$teamPlayerScores[] = $teamPlayerScore;
			}
			$team = new Team();
			$team->id = $row['teamId'];
			$team->teamName = $teamName;
			$playerScores = array();
			$teamPlayerScore = new TeamPlayerScores();
			$teamPlayerScore->team = $team;
			$lastTeam = $teamName;
		}
		$playerScore = new PlayerScore();
		$player = new Player();
		$player->playerFirstName = $row['firstName'];
		$player->playerSurname = $row['surname'];
		$player->team = $row['team'];
		$player->playerType = $row['playerType'];
		$playerScore->player = $player;
		$playerScore->score = $row['score'];

		$playerScores[] = $playerScore;
	}
	
	usort($playerScores, "comparePlayerScore");
				
	$teamPlayerScore->playerScores = $playerScores;
	$teamPlayerScores[] = $teamPlayerScore;
	
	
	return $teamPlayerScores;
}

function getTeamScoresByAverageForTeam($teamId, $round) {
	$sql = "select p.firstName, p.surname, p.team, p.playerType, ps.score, t.id as teamId, t.teamName from playerscore ps inner join player p on ps.playerId = p.id inner join teamplayer tp on tp.playerId = p.id ";
	$sql .= "and traded = false inner join team t on tp.teamId = t.id where ps.round in ($round) and t.id = $teamId;";
	
	$result = mysql_query($sql);
	
	$playerScores = array();
	$lastTeam = "";
	while($row = mysql_fetch_array($result)) {
		$teamName = $row['teamName'];
		$playerScore = new PlayerScore();
		$player = new Player();
		$player->playerFirstName = $row['firstName'];
		$player->playerSurname = $row['surname'];
		$player->team = $row['team'];
		$player->playerType = $row['playerType'];
		$playerScore->player = $player;
		$playerScore->score = $row['score'];

		$playerScores[] = $playerScore;
	}
	usort($playerScores, "comparePlayerScore");
			
	$team = new Team();
	$team->id = $row['teamId'];
	$team->teamName = $teamName;
	$teamPlayerScore = new TeamPlayerScores();
	$teamPlayerScore->team = $team;
	$teamPlayerScore->playerScores = $playerScores;
	
	
	return $teamPlayerScore;
}

function addPlayerScore($round, $playerId, $score) {
	$sql = "INSERT INTO playerscore (round, playerId, score) VALUES (" . $round . "," . $playerId . "," . $score . ");";
	mysql_query($sql);
	
}

function updateZeroScoringPlayers($round, $team) {
	$sql = "insert into playerscore (playerId, score, round) ";
	$sql .= "select id, 0, $round from player where id not in (select playerId from playerscore where round = $round) and team = '$team';";
	
	mysql_query($sql);
}

function hasPlayerScoreAlreadyForRound($playerId, $round) {
	$sql = "SELECT count(*) as count FROM playerscore where playerId = $playerId and round = $round";
	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);
	$rowCount = $row['count'];
	
	return $rowCount > 0; 
}

function getNumberOfTeamsSubmitted($round) {
        $sql = "SELECT count(distinct team) as count from playerscore ps inner join player p on ps.playerId = p.id where round = $round";
	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);
	
	return $row['count'];
}

function getPlayerInfo($playerId, $roundsSincePlayed) {
	$sql = "SELECT p.firstName, p.surname, p.team, p.playerType, round, score from playerscore ps inner join player p on ps.playerId = p.id where p.id = $playerId and round <= 49";
	$result = mysql_query($sql);
	
	$playerScores = array();
	$name = "";
	$scoreString = "";
	$totalGames = 0;
	$totalScore = 0;
	while($row = mysql_fetch_array($result)) {
		if ($name == "") {
			$name = $row['firstName'] . " " . $row['surname'] . " (" . getAFLTeam($row['team']) . ")";
		}
		$score = $row['score'];
        if ($score > 0) {
		   $scoreString .= "Rd " . $row['round'] . ": " . $score . "<br>";
		   $totalScore += $score;
                
		   $totalGames++;
        }
	}
	
	$average = 0;
        if ($totalGames > 0 ) {
           $average = round(floatval($totalScore) / floatval($totalGames));
        }
	
	$result = $name . " <p>" . "Average: " . $average . "<br>Total Games: " . $totalGames . "<br>";
	$result .= "Total Score: " . $totalScore . "<br>" . $scoreString;
	if ($roundsSincePlayed > 0) {
		$result .= "<br>Rounds since played: " . $roundsSincePlayed;
	}
	
	return $result;
}

function getRoundsSincePlayed($playerId) {
	$sql = "select score from playerscore where playerid = $playerId and round < 49 order by round desc";
	$result = mysql_query($sql);

	$count = 0;	
	while($row = mysql_fetch_array($result)) {
		if ($row['score'] == 0) {
			$count++;
		}
		else {
			break;
		}
	}
	
	return $count;
}
?>